﻿using He.Framework.AttributeMapping;
using He.Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace He.DAL
{
    public class SqlHelper
    {
        private static string ConnectionStringCustomers = ConfigurationManager.ConnectionStrings["Customers"].ConnectionString;

        #region CRUD
        public T Find<T>(int id)
        {
            Type type = typeof(T);
            //string columnStrings = string.Join(",", type.GetProperties().Select(p => $"[{p.GetMappingName()}]"));    
            //string sql = $@"SELECT  {columnStrings}
            //              FROM {type.GetMappingName()}
            //                where Id={id}";
            string sql = $"{SqlBuilder<T>.GetSql(SqlType.FindOne)}{id}";
            using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                conn.Open();
                var reader = command.ExecuteReader();
                if (reader.Read())
                {
                    T t = (T)Activator.CreateInstance(type);
                    foreach(var prop in type.GetProperties())
                    {
                        //prop.GetValue(t);
                        string propName = prop.GetMappingName();
                        prop.SetValue(t, reader[propName] is DBNull ? null : reader[propName]);
                    }
                    return t;
                }
                else
                {
                    throw new Exception("主键查询，没有结果！");
                }

            }

        } 

        public List<T> FindAll<T>()
        {
            List<T> tList = new List<T>();
            return tList;
        }

        public bool Insert<T>(T t)
        {
            Type type = typeof(T);
            //string columnStrings = string.Join(",", type.GetPropertiesWithoutKey().Select(p => $"[{p.GetMappingName()}]"));
            //string valueStrings = string.Join(",", type.GetPropertiesWithoutKey().Select(p => $"@{p.GetMappingName()}"));
            //string sql = $@"insert into [{type.GetMappingName()}] ({columnStrings}) values({valueStrings}) ";
            string sql = SqlBuilder<T>.GetSql(SqlType.Insert);
            IEnumerable<SqlParameter> paraList = type.GetPropertiesWithoutKey().Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t)??DBNull.Value));

            using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(paraList.ToArray());
                conn.Open();
                int iResult = command.ExecuteNonQuery();
                return iResult > 0;
            }
        }

        public bool Update<T>(T t) where T:BaseModel
        {
            Type type = typeof(T);
            //string columnString = string.Join(",", type.GetPropertiesWithoutKey().Select(p => $"{p.GetMappingName()}=@{p.GetMappingName()}"));
            //string sql = $"update [{type.GetMappingName()}] set {columnString} where Id={t.Id} ";
            string sql = SqlBuilder<T>.GetSql(SqlType.Update) + t.Id;

            var parameters = type.GetPropertiesWithoutKey().Select(p => new SqlParameter(p.GetMappingName(), p.GetValue(t)??DBNull.Value));
            using (SqlConnection conn =  new SqlConnection(ConnectionStringCustomers))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(parameters.ToArray());
                conn.Open();
                int iResult = command.ExecuteNonQuery();
                return iResult>0;
            }
            
        }

        private T ExecuteSql<T>(string sql, IEnumerable<SqlParameter> paraList, Func<SqlCommand, T> func)
        {
            //SqlTransaction trans = null;
            using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
            {
                try
                { 
                    SqlCommand command = new SqlCommand(sql, conn);
                    command.Parameters.AddRange(paraList.ToArray());
                    conn.Open();
                    //trans = conn.BeginTransaction();
                    T t = func.Invoke(command);
                    //trans.Commit();
                    return t;
                }
                catch(Exception ex)
                {
                    //if(trans != null)
                    //{
                    //    trans.Rollback();
                    //}
                    throw ex;
                }
            }
        }

        public bool Delete<T>(T t) where T: BaseModel
        {
            Type type = typeof(T);
            //string sql = $"Delete from [{type.GetMappingName()}] where Id=@Id;";
            string sql = SqlBuilder<T>.GetSql(SqlType.Delete);
            List<SqlParameter> paraList = new List<SqlParameter>()
            {
                new SqlParameter("@Id", t.Id)
            };
            return this.ExecuteSql<bool>(sql, paraList, command =>
            {
                int iResult = command.ExecuteNonQuery();
                return iResult > 0;
            });

        }

        public bool Delete<T>(int id) where T:BaseModel, new ()
        {
            return this.Delete<T>(new T()
            {
                Id = id
            });
        }

        public bool Delete<T>(IEnumerable<T> tList) where T : BaseModel
        {
            Type type = typeof(T);
            string idList = string.Join(",", tList.Select(t => $"[{t.Id}]"));
            string sql = $"delete from [{type.GetMappingName()}] where Id in ({idList}) ";
            return this.ExecuteSql<bool>(sql, new List<SqlParameter>(), command =>
            {
                int iResult = command.ExecuteNonQuery();
                return iResult == tList.Count();
            });
        }
        /// <summary>
        /// 条件删除 表达式目录树
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public bool Delete<T>(Expression<Func<T, bool>> expression)
        {
            Type type = typeof(T);
            string sql = $"delete from [{type.GetMappingName()}] where {1} ";
            return true;
        }

        #endregion

    }
}
